﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.CompilerServices;

namespace client
{
    class DBHandle
    {
        public static string URL = "Data Source=192.168.1.130;Initial Catalog=RedRomanticKTV;User ID=sa;Password=123456@qwe";   
        public DBHandle()
        {          

        }                  
        public bool findUser(string UserName, string Pwd)//根据用户名和密码查找用户
        
        {
            string sql = string.Format(@"SELECT COUNT(*)
                                      FROM [RedRomanticKTV].[dbo].[UserInfo]
                                      WHERE Username='{0}' and PassWord='{1}';", UserName, Pwd);
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                int count = (int)sqlCommand.ExecuteScalar();
                if (count == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
            finally
            {
                sqlConnection.Close();
            }

        }
        public bool AddSinger(SingerInfo singerInfo)//添加歌手信息
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql1 = string.Format(@"insert into SingerInfo values(N'{0}',{1},{2},N'{3}',N'{4}',{5},'{6}',N'{7}',{8});",
                         singerInfo.Name
                        , singerInfo.Gender
                        , singerInfo.SingerType
                        , singerInfo.Singerdescription
                        , singerInfo.SingerImg
                        , singerInfo.LastUpdateTime
                        , singerInfo.AdminName
                        , singerInfo.UpdateReason
                        , singerInfo.IsDelete);
                SqlCommand sqlCommand = new SqlCommand(sql1, sqlConnection);
                int result = sqlCommand.ExecuteNonQuery();
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                sqlConnection.Close();
            }
        }
        public SqlDataAdapter SelectInfoBySQl(int choes)//根据不同的Sql语句查询不同的数据
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            string sql = "";
            if (choes == -1)
            {
                sql = string.Format(@"select [id] ,[TypeName] from [SingerType];");//取得歌手类型
            }
            else if (choes == 0)
            {
                sql = string.Format(@"  SELECT [id]
                                        ,[Name]
	                                    FROM [RedRomanticKTV].[dbo].[SingerInfo]");//取得歌手名
            }
            else if (choes == 1)
            {
                sql = string.Format(@"    SELECT SI.Name
                                          ,SI.id
                                          ,ST.TypeName
                                          ,G.Gender
                                          ,SI.SingerImg 
                                          ,SI.Singerdescription
                                          ,SI.IsDelete
                                          FROM  SingerInfo AS SI,SingerType AS ST,Gender as G
                                          WHERE SI.SingerType=ST.id 
                                          AND SI.Gender=G.id
                                          AND SI.IsDelete=0
                                          ");
            }
            else if (choes == 2)
            {   //取得歌曲类型
                sql = string.Format(@"SELECT [id],[TypeName]FROM [RedRomanticKTV].[dbo].[SongsType];");
            }
            else if (choes == 7)
            {
                sql = string.Format(@"SELECT [SongsName]
                                   ,SongsInfo.id
                                  ,[Spell]
                                  ,[NameLength]
                                  ,ST.TypeName
                                  ,SI.[Name]
                                  ,[FileName]
                                  ,[playNum]
                                  ,SongsInfo.IsDelete
                                  FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                  WHERE SongsInfo.SongsType=ST.id
                                  and SongsInfo.IsDelete=0
                                  and SongsInfo.Singer=SI.id;");
            }
            else if (choes == 8)
            {
                sql = string.Format(@"SELECT [SongsName]
                                   ,SongsInfo.id
                                  ,[Spell]
                                  ,[NameLength]
                                  ,ST.TypeName
                                  ,SI.[Name]
                                  ,[FileName]
                                  ,[playNum]
                                  ,SongsInfo.IsDelete
                                  FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                  WHERE SongsInfo.SongsType=ST.id
                                  and SongsInfo.IsDelete=1
                                  and SongsInfo.Singer=SI.id;");
            }
            else if (choes == 9)
            {
                sql = string.Format(@"    SELECT SI.Name
                                          ,SI.id
                                          ,ST.TypeName
                                          ,G.Gender
                                          ,SI.SingerImg 
                                          ,SI.Singerdescription
                                          ,SI.IsDelete
                                          FROM  SingerInfo AS SI,SingerType AS ST,Gender as G
                                          WHERE SI.SingerType=ST.id 
                                          AND SI.Gender=G.id
                                          AND SI.IsDelete=1
                                          ");
            }
            else if (choes==10)
            {
                sql = string.Format(@"SELECT TOP (1000) [id] 
                                      ,[request] 
                                      ,[requestTime] 
                                      ,[isRead]
                                  FROM [RedRomanticKTV].[dbo].[UserRequest]
                                  WHERE [isRead]=0; ");
            }
            else if (choes == 11)
            {
                sql = string.Format(@"SELECT TOP (1000) [id] 
                                      ,[request] 
                                      ,[requestTime]
                                      ,[isRead]
                                  FROM [RedRomanticKTV].[dbo].[UserRequest]
                                  WHERE [isRead]=1; ");
            }

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            return sqlDataAdapter;
        }
        public SqlDataAdapter SelectInfoBySQl(int choes, SingerInfo singerInfo)//根据不同的Sql语句查询不同的数据
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            string sql = "";
            if (choes == -1)//查询歌手类型
            {
                sql = string.Format(@"select [id] ,[TypeName] from [SingerType];");
            }
            else if (choes == 1)//多表等值查询歌手信息
            {
                sql = string.Format(@"    SELECT SI.Name
                                          ,SI.id
                                          ,ST.TypeName
                                          ,G.Gender
                                          ,SI.SingerImg 
                                          ,SI.Singerdescription
                                          ,SI.IsDelete
                                          FROM  SingerInfo AS SI,SingerType AS ST,Gender as G
                                          WHERE SI.SingerType=ST.id 
                                          AND SI.Gender=G.id
                                          AND SI.IsDelete=0
                                          AND SI.Name LIKE N'%{0}%' 
                                          AND SI.SingerType LIKE N'%{1}%';", singerInfo.Name, singerInfo.SingerType);
            }
            else if (choes == 3)//单表查询歌手信息
            {
                sql = string.Format(@"select [id],[Name],[Gender],[SingerType],[SingerImg],[Singerdescription],[IsDelete]
                                      FROM [RedRomanticKTV].[dbo].[SingerInfo] 
                                      WHERE id={0} AND IsDelete=0;", singerInfo.Id);
            }
            else if (choes == 4)
            {
                sql = string.Format(@" SELECT [SongsName]
                                       ,SongsInfo.id
                                      ,[Spell]
                                      ,[NameLength]
                                      ,ST.TypeName
                                      ,SI.[Name]
                                      ,[FileName]
                                      ,[playNum]
                                      FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                      WHERE SongsInfo.SongsType=ST.id
                                      and SongsInfo.Singer=SI.id
                                      and SongsInfo.IsDelete=0
                                      and SI.Name like N'%{0}%';", singerInfo.Name);
            }
            else if (choes == 5)
            {
                sql = string.Format(@"   SELECT [SongsName]
                                            ,SongsInfo.id
                                          ,[Spell]
                                          ,[NameLength]
                                          ,ST.TypeName
                                          ,SI.[Name]
                                          ,[FileName]
                                          ,[playNum]
                                          FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                          WHERE SongsInfo.SongsType=ST.id
                                          and SongsInfo.Singer=SI.id
                                          and SongsInfo.IsDelete=0
                                          and SongsInfo.SongsName like N'%{0}%';", singerInfo.Name);
            }
            else if (choes == 6)
            {
                sql = string.Format(@"
                                   SELECT [SongsName]
                                    ,SongsInfo.id
                                  ,[Spell]
                                  ,[NameLength]
                                  ,ST.TypeName
                                  ,SI.[Name]
                                  ,[FileName]
                                  ,[playNum]
                                  FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                  WHERE SongsInfo.SongsType=ST.id
                                  and SongsInfo.Singer=SI.id
                                  and SongsInfo.IsDelete=0
                                  and SongsInfo.Spell like N'%{0}%';", singerInfo.Name);
            }
            else if (choes == 7)
            {
                sql = string.Format(@"SELECT [SongsName]
                                    ,SongsInfo.id
                                  ,[Spell]
                                  ,[NameLength]
                                  ,ST.TypeName
                                  ,SI.[Name]
                                  ,[FileName]
                                  ,[playNum]
                                  ,SongsInfo.IsDelete
                                  FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                  WHERE SongsInfo.SongsType=ST.id
                                  and SongsInfo.Singer=SI.id
                                  and SongsInfo.IsDelete=0;");
            }
            else if (choes == 8)
            {
                sql = string.Format(@"SELECT TOP (1000) [id]
                                  ,[SongsName]
                                  ,[Spell]
                                  ,[NameLength]
                                  ,[SongsType]
                                  ,[Singer]
                                  ,[FileName]
                                  ,[playNum]
                                  ,[LastUpdateTime]   
                                  ,[IsDelete]
                              FROM [RedRomanticKTV].[dbo].[SongsInfo]
                              WHERE id={0}
                              AND IsDelete=0;", singerInfo.Id);
            }
            else if (choes == 9)
            {
                sql = string.Format(@" SELECT [SongsName]
                                       ,SongsInfo.id
                                      ,[Spell]
                                      ,[NameLength]
                                      ,ST.TypeName
                                      ,SI.[Name]
                                      ,[FileName]
                                      ,[playNum]
                                      FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                      WHERE SongsInfo.SongsType=ST.id
                                      and SongsInfo.Singer=SI.id
                                      and SongsInfo.IsDelete=1
                                      and SI.Name like N'%{0}%';", singerInfo.Name);
            }
            else if (choes == 10)
            {
                sql = string.Format(@"   SELECT [SongsName]
                                            ,SongsInfo.id
                                          ,[Spell]
                                          ,[NameLength]
                                          ,ST.TypeName
                                          ,SI.[Name]
                                          ,[FileName]
                                          ,[playNum]
                                          FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                          WHERE SongsInfo.SongsType=ST.id
                                          and SongsInfo.Singer=SI.id
                                          and SongsInfo.IsDelete=1
                                          and SongsInfo.SongsName like N'%{0}%';", singerInfo.Name);
            }
            else if (choes == 11)
            {
                sql = string.Format(@"
                                   SELECT [SongsName]
                                    ,SongsInfo.id
                                  ,[Spell]
                                  ,[NameLength]
                                  ,ST.TypeName
                                  ,SI.[Name]
                                  ,[FileName]
                                  ,[playNum]
                                  FROM [SongsInfo] ,[SingerInfo] AS SI,[SongsType] AS ST
                                  WHERE SongsInfo.SongsType=ST.id
                                  and SongsInfo.Singer=SI.id
                                  and SongsInfo.IsDelete=1
                                  and SongsInfo.Spell like N'%{0}%';", singerInfo.Name);
            }
            else if (choes == 12)//多表等值查询歌手信息
            {
                sql = string.Format(@"    SELECT SI.Name
                                          ,SI.id
                                          ,ST.TypeName
                                          ,G.Gender
                                          ,SI.SingerImg 
                                          ,SI.Singerdescription
                                          ,SI.IsDelete
                                          FROM  SingerInfo AS SI,SingerType AS ST,Gender as G
                                          WHERE SI.SingerType=ST.id 
                                          AND SI.Gender=G.id
                                          AND SI.IsDelete=1
                                          AND SI.Name LIKE N'%{0}%' 
                                          AND SI.SingerType LIKE N'%{1}%';", singerInfo.Name, singerInfo.SingerType);
            }
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            return sqlDataAdapter;
        }

        public int editResult;//sqlCommandBuilder方法的执行结果返回值↓
        public SqlCommandBuilder sqlCommandBuilder(SqlDataAdapter sqlDataAdapter, DataSet dataSet, string tables)//更新数据方法
        {
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            editResult = sqlDataAdapter.Update(dataSet, tables);
            return commandBuilder;
        }
        public bool AddSongs(SongsInfo songsInfo)//添加歌曲信息
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql1 = string.Format(@"INSERT INTO [SongsInfo] 
        VALUES(N'{0}','{1}',{2},{3},{4},N'{5}',{6},{7},N'{8}',N'{9}',{10});"
                , songsInfo.SongsName
                , songsInfo.Spell
                , songsInfo.NameLength
                , songsInfo.SongsType
                , songsInfo.Singer
                , songsInfo.FileName
                , songsInfo.PlayNum
                , songsInfo.LastUpdateTime
                , songsInfo.AdminName
                , songsInfo.UpdateReason
                , songsInfo.IsDelete);
                SqlCommand sqlCommand = new SqlCommand(sql1, sqlConnection);
                int result = sqlCommand.ExecuteNonQuery();
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {

                return false;

            }
            finally
            {
                sqlConnection.Close();
            }
        }

        public bool RecycleSongs(int choes, int id)//恢复资源
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql1 = "";

                if (choes == 1)
                {
                    sql1 = string.Format("UPDATE SongsInfo SET IsDelete=0 WHERE id={0};", id);
                }
                else if (choes == 2)
                {
                    sql1 = string.Format("UPDATE SingerInfo SET IsDelete=0 WHERE id={0};", id);
                }
                SqlCommand sqlCommand = new SqlCommand(sql1, sqlConnection);
                int result = sqlCommand.ExecuteNonQuery();
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                sqlConnection.Close();
            }

        }

        public bool DeleteById(int choes)//根据id删除
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql1 = "";
                if (choes==1)
                {
                    sql1 = string.Format(@"DELETE FROM SongsInfo WHERE DATEDIFF(DAY,LastUpdateTime,GETDATE())>30 AND IsDelete=1;");
                }
                else if (choes==2)
                {
                    sql1 = string.Format(@"DELETE FROM SingerInfo WHERE DATEDIFF(DAY,LastUpdateTime,GETDATE())>30 AND IsDelete=1;");
                }
                SqlCommand sqlCommand = new SqlCommand();
                int result = sqlCommand.ExecuteNonQuery();
                if (result>0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;                
            }
            finally
            {
                sqlConnection.Close();
            }
        }               
        public bool UpdateLastUpdateTime(int id,int table)
        {
            string tablesname = "";
            if (table==1)
            {
                tablesname = "SingerInfo";
            }
            else if(table==2)
            {
                tablesname = "SongsInfo";
            }
            string sql1 = string.Format("UPDATE {0} SET LastUpdateTime=GETDATE() WHERE ID={1};",tablesname,id);
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(sql1,sqlConnection);
                int result = sqlCommand.ExecuteNonQuery();
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;                
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        public int HaveNewMessqge()
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql = string.Format("select count(*) from [UserRequest] where isRead=0;");
                SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection);
                int result=Convert.ToInt32(sqlCommand.ExecuteScalar());
                return result;
            }
            catch (Exception)
            {
                return -1;   
            }
            finally
            {
                sqlConnection.Close();
            }
        }
        public bool IsRead(int id)
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql = string.Format("update UserRequest set isRead=1 where id={0};",id);
                SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection);
                int result = sqlCommand.ExecuteNonQuery();
                if (result>0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                sqlConnection.Close();
            }
        }
    }
    
}
